package zy.dao.buy.enter.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.buy.enter.EnterDAO;
import zy.entity.base.product.T_Base_Barcode;
import zy.entity.base.product.T_Base_Product;
import zy.entity.base.size.T_Base_Size;
import zy.entity.buy.enter.T_Buy_Enter;
import zy.entity.buy.enter.T_Buy_EnterList;
import zy.entity.buy.order.T_Buy_Import;
import zy.entity.buy.order.T_Buy_Order;
import zy.entity.buy.order.T_Buy_Product;
import zy.entity.stock.data.T_Stock_DataBill;
import zy.entity.sys.user.T_Sys_User;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class EnterDAOImpl extends BaseDaoImpl implements EnterDAO{

	@Override
	public Map<String, Object> countSum(Map<String, Object> params) {
		Object et_type = params.get("et_type");
		Object et_isdraft = params.get("et_isdraft");
		Object et_ar_state = params.get("et_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object et_supply_code = params.get("et_supply_code");
		Object et_depot_code = params.get("et_depot_code");
		Object et_manager = params.get("et_manager");
		Object et_number = params.get("et_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1) AS totalCount,");
		sql.append(" IFNULL(SUM(ABS(et_amount)),0) AS et_amount,IFNULL(SUM(ABS(et_money)),0) AS et_money,IFNULL(SUM(ABS(et_retailmoney)),0) AS et_retailmoney");
		sql.append(" FROM t_buy_enter t");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(et_type)) {
			sql.append(" AND et_type = :et_type ");
		}
		if (StringUtil.isNotEmpty(et_isdraft)) {
			sql.append(" AND et_isdraft = :et_isdraft ");
		}
		if (StringUtil.isNotEmpty(et_ar_state)) {
			sql.append(" AND et_ar_state = :et_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND et_make_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND et_make_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(et_supply_code)) {
			sql.append(" AND et_supply_code = :et_supply_code ");
		}
		if (StringUtil.isNotEmpty(et_depot_code)) {
			sql.append(" AND et_depot_code = :et_depot_code ");
		}else if(CommonUtil.THREE.equals(params.get(CommonUtil.SHOP_TYPE))){//自营店
			sql.append(" AND et_depot_code IN (:depot_codes) ");
		}
		if (StringUtil.isNotEmpty(et_manager)) {
			sql.append(" AND et_manager = :et_manager ");
		}
		if (StringUtil.isNotEmpty(et_number)) {
			sql.append(" AND INSTR(et_number,:et_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<T_Buy_Enter> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object et_type = params.get("et_type");
		Object et_isdraft = params.get("et_isdraft");
		Object et_ar_state = params.get("et_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object et_supply_code = params.get("et_supply_code");
		Object et_depot_code = params.get("et_depot_code");
		Object et_manager = params.get("et_manager");
		Object et_number = params.get("et_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT et_id,et_number,et_make_date,et_supply_code,et_depot_code,et_maker,et_manager,et_handnumber,ABS(et_amount) AS et_amount,ABS(et_money) AS et_money,ABS(et_retailmoney) AS et_retailmoney,");
		sql.append(" et_discount_money,et_property,et_remark,et_ar_state,et_ar_date,et_ar_name,et_isdraft,et_pay_state,et_payable,et_payabled,et_prepay,");
		sql.append(" et_order_number,et_type,et_sysdate,et_us_id,companyid,");
		sql.append(" (SELECT sp_name FROM t_buy_supply sp WHERE sp_code = et_supply_code AND sp.companyid = t.companyid LIMIT 1) AS supply_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = et_depot_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name");
		sql.append(" FROM t_buy_enter t");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(et_type)) {
			sql.append(" AND et_type = :et_type ");
		}
		if (StringUtil.isNotEmpty(et_isdraft)) {
			sql.append(" AND et_isdraft = :et_isdraft ");
		}
		if (StringUtil.isNotEmpty(et_ar_state)) {
			sql.append(" AND et_ar_state = :et_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND et_make_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND et_make_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(et_supply_code)) {
			sql.append(" AND et_supply_code = :et_supply_code ");
		}
		if (StringUtil.isNotEmpty(et_depot_code)) {
			sql.append(" AND et_depot_code = :et_depot_code ");
		}else if(CommonUtil.THREE.equals(params.get(CommonUtil.SHOP_TYPE))){//自营店
			sql.append(" AND et_depot_code IN (:depot_codes) ");
		}
		if (StringUtil.isNotEmpty(et_manager)) {
			sql.append(" AND et_manager = :et_manager ");
		}
		if (StringUtil.isNotEmpty(et_number)) {
			sql.append(" AND INSTR(et_number,:et_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY et_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_Enter.class));	
	}
	
	@Override
	public T_Buy_Enter load(Integer et_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT et_id,et_number,et_make_date,et_supply_code,et_depot_code,et_maker,et_manager,et_handnumber,et_amount,et_money,et_retailmoney,");
		sql.append(" et_discount_money,et_property,et_remark,et_ar_state,et_ar_date,et_ar_name,et_isdraft,et_pay_state,et_payable,et_payabled,et_prepay,");
		sql.append(" et_order_number,et_type,et_sysdate,et_us_id,companyid,");
		sql.append(" (SELECT sp_name FROM t_buy_supply sp WHERE sp_code = et_supply_code AND sp.companyid = t.companyid LIMIT 1) AS supply_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = et_depot_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name");
		sql.append(" FROM t_buy_enter t");
		sql.append(" WHERE et_id = :et_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("et_id", et_id),
					new BeanPropertyRowMapper<>(T_Buy_Enter.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Buy_Enter load(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT et_id,et_number,et_make_date,et_supply_code,et_depot_code,et_maker,et_manager,et_handnumber,et_amount,et_money,et_retailmoney,");
		sql.append(" et_discount_money,et_property,et_remark,et_ar_state,et_ar_date,et_ar_name,et_isdraft,et_pay_state,et_payable,et_payabled,et_prepay,");
		sql.append(" et_order_number,et_type,et_sysdate,et_us_id,companyid,");
		sql.append(" (SELECT sp_name FROM t_buy_supply sp WHERE sp_code = et_supply_code AND sp.companyid = t.companyid LIMIT 1) AS supply_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = et_depot_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name");
		sql.append(" FROM t_buy_enter t");
		sql.append(" WHERE et_number = :et_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("et_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Enter.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Buy_Enter check(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT et_id,et_number,et_supply_code,et_depot_code,et_ar_state,et_type,et_amount,et_discount_money,et_payable,et_pay_state,et_remark,companyid");
		sql.append(" FROM t_buy_enter t");
		sql.append(" WHERE et_number = :et_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("et_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Enter.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Buy_EnterList> detail_list_forsavetemp(String et_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT etl_id,etl_number,etl_pd_code,etl_sub_code,etl_sz_code,etl_szg_code,etl_cr_code,etl_br_code,etl_amount,etl_unitprice,etl_retailprice,");
		sql.append(" etl_pi_type,etl_remark,etl_order_number,etl_type,t.companyid");
		sql.append(" FROM t_buy_enterlist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_number = :etl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY etl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("etl_number", et_number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
	}
	
	@Override
	public List<T_Buy_EnterList> detail_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT etl_id,etl_number,etl_pd_code,etl_sub_code,etl_sz_code,etl_szg_code,etl_cr_code,etl_br_code,ABS(etl_amount) AS etl_amount,etl_unitprice,etl_retailprice,");
		sql.append(" etl_pi_type,etl_remark,etl_order_number,etl_type,t.companyid,pd_no,pd_name,pd_unit,cr_name,sz_name,br_name");
		sql.append(" FROM t_buy_enterlist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.etl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_number = :etl_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY etl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
	}
	
	@Override
	public List<T_Buy_EnterList> detail_list_print(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT etl_id,etl_number,etl_pd_code,etl_sub_code,etl_sz_code,etl_szg_code,etl_cr_code,etl_br_code,ABS(etl_amount) AS etl_amount,etl_unitprice,etl_retailprice,");
		sql.append(" etl_pi_type,etl_remark,etl_order_number,etl_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year,cr_name,sz_name,br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_buy_enterlist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.etl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_number = :etl_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY etl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
	}
	
	@Override
	public List<T_Buy_EnterList> detail_sum(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT etl_id,etl_pd_code,etl_szg_code,SUM(ABS(etl_amount)) AS etl_amount,etl_unitprice, etl_retailprice,");
		sql.append(" etl_remark,etl_pi_type,etl_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_buy_enterlist t ");
		sql.append(" JOIN t_base_product pd ON pd_code = t.etl_pd_code AND pd.companyid = t.companyid");  
		sql.append(" WHERE 1=1 ");
		sql.append(" AND etl_number = :etl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY etl_pd_code,etl_pi_type");
		sql.append(" ORDER BY etl_pd_code,etl_pi_type ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
	}
	
	@Override
	public List<String> detail_szgcode(Map<String,Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT etl_szg_code");
		sql.append(" FROM t_buy_enterlist t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND etl_number = :etl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public List<T_Buy_EnterList> list_order_forimport(List<Integer> ids) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT odl_number AS etl_order_number,odl_pd_code AS etl_pd_code,odl_sub_code AS etl_sub_code,odl_szg_code AS etl_szg_code,");
		sql.append(" odl_sz_code AS etl_sz_code,odl_cr_code AS etl_cr_code,odl_br_code AS etl_br_code,odl_amount-odl_realamount AS etl_amount,");
		sql.append(" odl_unitprice AS etl_unitprice,odl_retailprice AS etl_retailprice,odl_pi_type AS etl_pi_type,odl_type AS etl_type,companyid");
		sql.append(" FROM t_buy_orderlist");
		sql.append(" WHERE odl_id IN(:ids)");
		sql.append(" AND odl_amount > odl_realamount");
		return namedParameterJdbcTemplate.query(sql.toString(),new MapSqlParameterSource().addValue("ids", ids),
				new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
	}
	
	@Override
	public List<T_Buy_EnterList> temp_list_forimport(Integer et_type,Integer us_id,Integer companyid) {//只查询商品
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT etl_id,etl_pd_code,etl_sub_code,etl_sz_code,etl_szg_code,etl_cr_code,etl_br_code,etl_amount,etl_unitprice,etl_retailprice,");
		sql.append(" etl_pi_type,etl_remark,etl_order_number,etl_type,etl_us_id,t.companyid ");
		sql.append(" FROM t_buy_enterlist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_pi_type = :etl_pi_type");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("etl_pi_type", 0).addValue("etl_type", et_type).addValue("etl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
	}
	
	@Override
	public List<T_Buy_EnterList> temp_list_forsave(Integer et_type,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT etl_id,etl_pd_code,etl_sub_code,etl_sz_code,etl_szg_code,etl_cr_code,etl_br_code,etl_amount,etl_unitprice,etl_retailprice,");
		sql.append(" etl_pi_type,etl_remark,etl_order_number,etl_type,etl_us_id,t.companyid, ");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = etl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = etl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = etl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_buy_enterlist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY etl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("etl_type", et_type).addValue("etl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
	}
	
	@Override
	public List<T_Buy_EnterList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT etl_id,etl_pd_code,etl_sub_code,etl_sz_code,etl_szg_code,etl_cr_code,etl_br_code,etl_amount,etl_unitprice,etl_retailprice,");
		sql.append(" etl_pi_type,etl_remark,etl_order_number,etl_type,etl_us_id,t.companyid,pd_no,pd_name,pd_unit,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = etl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = etl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = etl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_buy_enterlist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.etl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY etl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
	}
	
	@Override
	public List<T_Buy_EnterList> temp_sum(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT etl_id,etl_pd_code,etl_szg_code,SUM(etl_amount) AS etl_amount,etl_unitprice, etl_retailprice,");
		sql.append(" etl_remark,etl_pi_type,etl_us_id,etl_type,t.companyid,pd_no,pd_name,pd_unit,  ");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_buy_enterlist_temp t ");
		sql.append(" JOIN t_base_product pd ON pd_code = t.etl_pd_code AND pd.companyid = t.companyid");  
		sql.append(" WHERE 1=1 ");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY etl_pd_code,etl_pi_type");
		sql.append(" ORDER BY etl_pd_code,etl_pi_type ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
	}
	
	@Override
	public List<String> temp_szgcode(Map<String,Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT etl_szg_code");
		sql.append(" FROM t_buy_enterlist_temp t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public Integer count_product(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT count(1)");
		sql.append(" FROM (");
		sql.append(" SELECT 1 FROM t_base_product t");
		sql.append(" LEFT JOIN t_buy_enterlist_temp etl ON etl_pd_code = pd_code AND etl.companyid = t.companyid AND etl_type = :et_type AND etl_us_id = :us_id");
		sql.append(" where 1 = 1");
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND etl_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code)t");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
	}

	@Override
	public List<T_Base_Product> list_product(Map<String, Object> param) {
		Object sidx = param.get(CommonUtil.SIDX);
		Object sord = param.get(CommonUtil.SORD);
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_id,pd_code,pd_no,pd_name,IF(etl_id IS NULL,0,1) AS exist");
		sql.append(" FROM t_base_product t");
		sql.append(" LEFT JOIN t_buy_enterlist_temp etl ON etl_pd_code = pd_code AND etl.companyid = t.companyid AND etl_type = :et_type AND etl_us_id = :us_id");
		sql.append(" where 1 = 1");
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND etl_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY pd_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Product.class));
	}
	
	@Override
	public T_Base_Product load_product(String pd_code,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pd.pd_id,pd.pd_code,pd_no,pd_name,pd_szg_code,pd_unit,pd_year,pd_season,pd_sell_price,pd_buy_price,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = pd.companyid LIMIT 1) AS pd_bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = pd.companyid LIMIT 1) AS pd_tp_name");
		sql.append(" ,(SELECT pdm_img_path FROM t_base_product_img pdm WHERE pdm_pd_code = pd.pd_code AND pdm.companyid = pd.companyid LIMIT 1) AS pdm_img_path");
		sql.append(" FROM t_base_product pd");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd.pd_code = :pd_code");
		sql.append(" AND pd.companyid = :companyid");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pd_code", pd_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Product.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public Map<String, Object> load_product_size(Map<String,Object> params) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		//1.查询尺码信息
		List<T_Base_Size> sizes = namedParameterJdbcTemplate.query(getSizeSQL(), params, new BeanPropertyRowMapper<>(T_Base_Size.class));
		resultMap.put("sizes",sizes);
		//2.获取颜色杯型信息
		List<T_Buy_Product> inputs = namedParameterJdbcTemplate.query(getColorBraSQL(), params, new BeanPropertyRowMapper<>(T_Buy_Product.class));
		resultMap.put("inputs",inputs);
		//3.已录入数量
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT etl_sz_code AS sz_code,etl_cr_code AS cr_code,etl_br_code AS br_code,etl_amount AS amount");
		sql.append(" FROM t_buy_enterlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_pd_code = :pd_code");
		sql.append(" AND etl_us_id = :us_id");
		sql.append(" AND etl_pi_type = :etl_pi_type");
		sql.append(" AND etl_type = :et_type");
		sql.append(" AND companyid = :companyid");
		List<T_Buy_Product> temps = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_Product.class));
		resultMap.put("temps",temps);
		//4.库存数量
		List<T_Buy_Product> stocks = namedParameterJdbcTemplate.query(getStockSQL(), params, new BeanPropertyRowMapper<>(T_Buy_Product.class));
		resultMap.put("stocks",stocks);
		return resultMap;
	}
	
	@Override
	public Double temp_queryUnitPrice(String pd_code,String etl_pi_type, Integer et_type, Integer us_id, Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT etl_unitprice");
		sql.append(" FROM t_buy_enterlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_pd_code = :etl_pd_code");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_pi_type = :etl_pi_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("etl_pd_code", pd_code)
							.addValue("etl_type", et_type)
							.addValue("etl_pi_type", etl_pi_type)
							.addValue("etl_us_id", us_id)
							.addValue("companyid", companyid), Double.class);
		} catch (Exception e) {
			return null;
		}
		
	}

	@Override
	public void temp_save(List<T_Buy_EnterList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_buy_enterlist_temp");
		sql.append(" (etl_pd_code,etl_sub_code,etl_sz_code,etl_szg_code,etl_cr_code,etl_br_code,etl_amount,");
		sql.append(" etl_unitprice,etl_retailprice,etl_pi_type,etl_remark,etl_order_number,etl_type,etl_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:etl_pd_code,:etl_sub_code,:etl_sz_code,:etl_szg_code,:etl_cr_code,:etl_br_code,:etl_amount,");
		sql.append(" :etl_unitprice,:etl_retailprice,:etl_pi_type,:etl_remark,:etl_order_number,:etl_type,:etl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}

	@Override
	public void temp_save(T_Buy_EnterList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_buy_enterlist_temp");
		sql.append(" (etl_pd_code,etl_sub_code,etl_sz_code,etl_szg_code,etl_cr_code,etl_br_code,etl_amount,");
		sql.append(" etl_unitprice,etl_retailprice,etl_pi_type,etl_remark,etl_order_number,etl_type,etl_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:etl_pd_code,:etl_sub_code,:etl_sz_code,:etl_szg_code,:etl_cr_code,:etl_br_code,:etl_amount,");
		sql.append(" :etl_unitprice,:etl_retailprice,:etl_pi_type,:etl_remark,:etl_order_number,:etl_type,:etl_us_id,:companyid)");;
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp),holder);
		temp.setEtl_id(holder.getKey().intValue());
	}
	
	@Override
	public void temp_update(List<T_Buy_EnterList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_enterlist_temp");
		sql.append(" SET etl_amount = :etl_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_sub_code = :etl_sub_code");
		sql.append(" AND etl_pi_type = :etl_pi_type");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_updateById(List<T_Buy_EnterList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_enterlist_temp");
		sql.append(" SET etl_amount = :etl_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_id = :etl_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_update(T_Buy_EnterList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_enterlist_temp");
		sql.append(" SET etl_amount = :etl_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_id = :etl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateRemarkById(T_Buy_EnterList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_enterlist_temp");
		sql.append(" SET etl_remark = :etl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_id = :etl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateRemarkByPdCode(T_Buy_EnterList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_enterlist_temp");
		sql.append(" SET etl_remark = :etl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_pd_code = :etl_pd_code");
		sql.append(" AND etl_pi_type = :etl_pi_type");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateprice(String pd_code,String etl_pi_type, Double unitPrice, Integer et_type, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_enterlist_temp");
		sql.append(" SET etl_unitprice = :etl_unitprice");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_pd_code = :etl_pd_code");
		sql.append(" AND etl_pi_type = :etl_pi_type");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource()
						.addValue("etl_unitprice", unitPrice)
						.addValue("etl_pd_code", pd_code)
						.addValue("etl_pi_type", etl_pi_type)
						.addValue("etl_type", et_type)
						.addValue("etl_us_id", us_id)
						.addValue("companyid", companyid));
	}

	@Override
	public void temp_del(List<T_Buy_EnterList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_buy_enterlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_sub_code = :etl_sub_code");
		sql.append(" AND etl_pi_type = :etl_pi_type");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_del(Integer etl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_buy_enterlist_temp");
		sql.append(" WHERE etl_id=:etl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("etl_id", etl_id));
	}
	
	@Override
	public void temp_delByPiCode(T_Buy_EnterList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_buy_enterlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_pd_code = :etl_pd_code");
		if(StringUtil.isNotEmpty(temp.getEtl_cr_code())){
			sql.append(" AND etl_cr_code = :etl_cr_code");
		}
		if(StringUtil.isNotEmpty(temp.getEtl_br_code())){
			sql.append(" AND etl_br_code = :etl_br_code");
		}
		sql.append(" AND etl_pi_type = :etl_pi_type");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_clear(Integer et_type,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_buy_enterlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("etl_type", et_type)
						.addValue("etl_us_id", us_id)
						.addValue("companyid", companyid));
	}
	
	@Override
	public T_Buy_EnterList temp_loadBySubCode(String sub_code,String etl_pi_type, Integer et_type, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT etl_id,etl_pd_code,etl_sub_code,etl_sz_code,etl_szg_code,etl_cr_code,etl_br_code,etl_amount,etl_unitprice,");
		sql.append(" etl_retailprice,etl_remark,etl_pi_type,etl_us_id,etl_type,companyid ");
		sql.append(" FROM t_buy_enterlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_sub_code = :etl_sub_code");
		sql.append(" AND etl_pi_type = :etl_pi_type");
		sql.append(" AND etl_type = :etl_type");
		sql.append(" AND etl_us_id = :etl_us_id");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource()
							.addValue("etl_sub_code", sub_code)
							.addValue("etl_type", et_type)
							.addValue("etl_pi_type", etl_pi_type)
							.addValue("etl_us_id", us_id)
							.addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public List<T_Buy_Import> temp_listByImport(List<String> barCodes,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT bc_pd_code,bc_subcode,bc_barcode,bc_size,pd_szg_code,bc_color,bc_bra,pd_sell_price AS retail_price,pd_buy_price AS unit_price");
		sql.append(" FROM t_base_barcode bc");
		sql.append(" JOIN t_base_product pd ON pd.pd_code = bc_pd_code AND pd.companyid = bc.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND bc_barcode IN(:barcode)");
		sql.append(" AND bc.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("barcode", barCodes).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_Import.class));
	}
	
	@Override
	public void save(T_Buy_Enter enter) {
		String prefix = CommonUtil.NUMBER_PREFIX_BUY_ENTER_IN + DateUtil.getYearMonthDateYYYYMMDD();
		if(enter.getEt_type().equals(1)){
			prefix = CommonUtil.NUMBER_PREFIX_BUY_ENTER_OUT + DateUtil.getYearMonthDateYYYYMMDD();
		}
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(et_number))) AS new_number");
		sql.append(" FROM t_buy_enter");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(et_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", enter.getCompanyid()), String.class);
		enter.setEt_number(new_number);
		sql.setLength(0);
		sql.append(" INSERT INTO t_buy_enter");
		sql.append(" (et_number,et_make_date,et_supply_code,et_depot_code,et_maker,et_manager,et_handnumber,et_amount,et_money,et_retailmoney,");
		sql.append(" et_discount_money,et_ba_code,et_property,et_remark,et_ar_state,et_isdraft,et_pay_state,et_payable,et_payabled,et_prepay,");
		sql.append(" et_order_number,et_type,et_sysdate,et_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:et_number,:et_make_date,:et_supply_code,:et_depot_code,:et_maker,:et_manager,:et_handnumber,:et_amount,:et_money,");
		sql.append(" :et_retailmoney,:et_discount_money,:et_ba_code,:et_property,:et_remark,:et_ar_state,:et_isdraft,:et_pay_state,:et_payable,");
		sql.append(" :et_payabled,:et_prepay,:et_order_number,:et_type,:et_sysdate,:et_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(enter),holder);
		enter.setEt_id(holder.getKey().intValue());
	}
	
	@Override
	public void update(T_Buy_Enter enter) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_buy_enter");
		sql.append(" SET et_make_date=:et_make_date");
		sql.append(" ,et_supply_code=:et_supply_code");
		sql.append(" ,et_depot_code=:et_depot_code");
		sql.append(" ,et_maker=:et_maker");
		sql.append(" ,et_manager=:et_manager");
		sql.append(" ,et_handnumber=:et_handnumber");
		sql.append(" ,et_amount=:et_amount");
		sql.append(" ,et_money=:et_money");
		sql.append(" ,et_retailmoney=:et_retailmoney");
		sql.append(" ,et_discount_money=:et_discount_money");
		sql.append(" ,et_payable=:et_payable");
		sql.append(" ,et_property=:et_property");
		sql.append(" ,et_remark=:et_remark");
		sql.append(" ,et_ar_state=:et_ar_state");
		sql.append(" ,et_order_number=:et_order_number");
		sql.append(" ,et_us_id=:et_us_id");
		sql.append(" WHERE et_id=:et_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(enter));
	}
	
	@Override
	public void saveList(List<T_Buy_EnterList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_buy_enterlist");
		sql.append(" (etl_number,etl_pd_code,etl_sub_code,etl_szg_code,etl_sz_code,etl_cr_code,etl_br_code,etl_amount,etl_unitprice,etl_retailprice,etl_pi_type,etl_remark,etl_order_number,etl_type,companyid,cr_name,sz_name,br_name)");
		sql.append(" VALUES");
		sql.append(" (:etl_number,:etl_pd_code,:etl_sub_code,:etl_szg_code,:etl_sz_code,:etl_cr_code,:etl_br_code,:etl_amount,:etl_unitprice,:etl_retailprice,:etl_pi_type,:etl_remark,:etl_order_number,:etl_type,:companyid,:cr_name,:sz_name,:br_name)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateApprove(T_Buy_Enter enter) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_buy_enter");
		sql.append(" SET et_ar_state=:et_ar_state");
		sql.append(" ,et_ar_date = :et_ar_date");
		sql.append(" ,et_ar_name = :et_ar_name");
		sql.append(" WHERE et_id=:et_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(enter));
	}

	@Override
	public List<T_Buy_EnterList> listWithOrder(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT etl_number,etl_sub_code,ABS(etl_amount) AS etl_amount,etl_pi_type,etl_order_number,companyid");
		sql.append(" FROM t_buy_enterlist");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_number = :etl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" AND etl_order_number IS NOT NULL");
		sql.append(" AND etl_order_number != ''");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("etl_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
	}
	
	@Override
	public T_Buy_Order loadOrder(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT od_id,od_number,od_amount,od_realamount,od_delivery_date");
		sql.append(" FROM t_buy_order t");
		sql.append(" WHERE od_number = :od_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("od_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Order.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public void updateOrder(T_Buy_Order order) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_buy_order");
		sql.append(" SET od_state=:od_state");
		sql.append(" ,od_realamount = :od_realamount");
		sql.append(" WHERE od_id=:od_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(order));
	}
	
	@Override
	public void updateOrderList(List<T_Buy_EnterList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_orderlist");
		sql.append(" SET odl_realamount = odl_realamount + :etl_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND odl_sub_code = :etl_sub_code");
		sql.append(" AND odl_pi_type = :etl_pi_type");
		sql.append(" AND odl_number = :etl_order_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateOrderList_Reverse(List<T_Buy_EnterList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_orderlist");
		sql.append(" SET odl_realamount = odl_realamount - :etl_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND odl_sub_code = :etl_sub_code");
		sql.append(" AND odl_pi_type = :etl_pi_type");
		sql.append(" AND odl_number = :etl_order_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public List<T_Buy_EnterList> listGroupProduct(String number, Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT etl_pd_code,SUM(etl_amount) AS etl_amount, ");
		sql.append(" (SELECT etl_unitprice FROM t_buy_enterlist etl ");
		sql.append(" WHERE etl_number = :etl_number AND etl.companyid = :companyid AND etl.etl_pd_code = t.etl_pd_code ORDER BY etl.etl_type LIMIT 1) AS etl_unitprice");//查询商品价格
		sql.append(" FROM t_buy_enterlist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_number = :etl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY etl_pd_code");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("etl_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_EnterList.class));
	}
	
	@Override
	public List<T_Stock_DataBill> listProductStock(List<String> pd_codes,T_Sys_User user){
		Map<String, Object> params = new HashMap<String, Object>();
		params.put(CommonUtil.SHOP_CODE, user.getUs_shop_code());
		params.put(CommonUtil.SHOP_TYPE, user.getShoptype());
		params.put("companyid", user.getCompanyid());
		params.put("pd_codes", pd_codes);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT sd_pd_code,SUM(ABS(sd_amount)) AS sd_amount,");
		sql.append(" (SELECT pd_cost_price FROM t_base_product pd WHERE pd_code = sd_pd_code AND pd.companyid = t.companyid LIMIT 1) AS pd_cost_price");
		sql.append(" FROM t_stock_data t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_dp_code IN(");
		sql.append(" SELECT dp_code FROM t_base_depot dp");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND dp.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(user.getShoptype()) || CommonUtil.TWO.equals(user.getShoptype())){//总公司、分公司
			sql.append(getShopSQL(user.getShoptype(), 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND dp.companyid = :companyid)");
		sql.append(" AND sd_pd_code IN(:pd_codes)");
		sql.append(" AND sd_amount <> 0");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY sd_pd_code");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_DataBill.class));
	}
	
	@Override
	public void updateProductBuyPriceAndDate(List<T_Base_Product> products) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_base_product");
		sql.append(" SET pd_buy_date = :pd_buy_date");
		sql.append(" ,pd_buy_price = :pd_buy_price");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd_code = :pd_code");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(products.toArray()));
	}
	@Override
	public void updateProductCostPrice(List<T_Base_Product> products) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_base_product");
		sql.append(" SET pd_cost_price = :pd_cost_price");
		sql.append(" WHERE pd_code = :pd_code");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(products.toArray()));
	}
	
	@Override
	public List<T_Stock_DataBill> listStock(String number,String dp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT etl_pd_code AS sd_pd_code,etl_sub_code AS sd_code,etl_cr_code AS sd_cr_code,etl_sz_code AS sd_sz_code,");
		sql.append(" etl_br_code AS sd_br_code,SUM(ABS(etl_amount)) AS bill_amount,sd_id,sd_amount");
		sql.append(" FROM t_buy_enterlist etl");
		sql.append(" LEFT JOIN t_stock_data sd ON etl_sub_code = sd_code AND etl.companyid = sd.companyid AND sd.sd_dp_code = :dp_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_number = :etl_number");
		sql.append(" AND etl.companyid = :companyid");
		sql.append(" GROUP BY etl_sub_code");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("etl_number", number).addValue("dp_code", dp_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_DataBill.class));
	}

	@Override
	public void del(String et_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_buy_enter");
		sql.append(" WHERE et_number=:et_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("et_number", et_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_buy_enterlist");
		sql.append(" WHERE etl_number=:etl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("etl_number", et_number).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteList(String et_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_buy_enterlist");
		sql.append(" WHERE etl_number=:etl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("etl_number", et_number).addValue("companyid", companyid));
	}

	@Override
	public List<T_Base_Barcode> print_barcode(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select etl_id as id,bc_id,pd.pd_code AS bc_pd_code,pd.pd_no AS bc_pd_no,etl_sz_code AS bc_size,etl_br_code AS bc_bra,etl_cr_code AS bc_color,");
		sql.append(" bc_subcode,bc_sys_barcode,bc_barcode,pd.pd_name as bc_pd_name,pd.pd_sell_price,etl_amount AS bc_printnumber,");
		sql.append(" pd_vip_price,pd_sign_price,pd_grade,pd_safe,pd_fill,pd_execute,pd_fabric,pd_in_fabric,pd_place,pd_wash_explain,");
		sql.append("(select bd_name from t_base_brand bd where bd.bd_code=pd.pd_bd_code and bd.companyid=pd.companyid LIMIT 1) as pd_bd_name,");//品牌
		sql.append("(select tp_name from t_base_type tp where tp.tp_code=pd.pd_tp_code and tp.companyid=pd.companyid LIMIT 1) as pd_tp_name,");//类别
		sql.append("(select cr_name from t_base_color color where color.cr_code=t.etl_cr_code and color.companyid=t.companyid LIMIT 1) as bc_colorname,");//颜色
		sql.append("(select sz_name from t_base_size size where size.sz_code=t.etl_sz_code and size.companyid=t.companyid LIMIT 1) as bc_sizename,");//尺码
		sql.append("(select br_name from t_base_bra bra where bra.br_code=t.etl_br_code and bra.companyid=t.companyid LIMIT 1) as bc_braname");//杯型
		sql.append(" FROM t_buy_enterlist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.etl_pd_code AND pd.companyid = t.companyid");
		sql.append(" join t_base_product_info info ON info.pd_code = pd.pd_code AND info.companyid = pd.companyid ");
		sql.append(" LEFT JOIN t_base_barcode bc ON bc.bc_subcode = t.etl_sub_code AND bc.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND etl_number = :etl_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY etl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Barcode.class));
	}
	
}
